package com.unis.common.excel;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

public class ImportExcelToData<T> {
    private static Logger logger = LoggerFactory.getLogger(ExportBeanExcel.class);
    /**
     * 导入Excel
     * @param file
     */
    public static List<String[]> impExcel(MultipartFile file){
        List<String[]> list = new ArrayList<>();
        try {
            File fo = new File(file.getOriginalFilename());
            FileUtils.copyInputStreamToFile(file.getInputStream(),fo);
            // 构造 Workbook 对象，execelFile 是传入文件路径(获得Excel工作区)
            Workbook book = null;
            try {
                // Excel 2007获取方法
                book = new XSSFWorkbook(FileUtils.openInputStream(fo));
            } catch (Exception ex) {
                // Excel 2003获取方法
                book = new HSSFWorkbook(FileUtils.openInputStream(fo));
            }

            // 读取表格的第一个sheet页
            Sheet sheet = book.getSheetAt(0);
            // 定义 row、cell
            Row row;
            String cell;
            // 总共有多少行,从0开始
            int totalRows = sheet.getLastRowNum() ;
            // 循环输出表格中的内容,首先循环取出行,再根据行循环取出列
            for (int i = 0; i <= totalRows; i++) {
                row = sheet.getRow(i);
                // 处理空行
                if(row == null){
                    continue ;
                }
                // 总共有多少列,从0开始
                int totalCells = row.getLastCellNum() ;
                StringBuffer stringBuffer = new StringBuffer();
                for (int j = row.getFirstCellNum(); j < totalCells; j++) {
                    // 处理空列
                    /*if(row.getCell(j) == null){
                        continue ;
                    }*/
                    // 通过 row.getCell(j).toString() 获取单元格内容
                    //cell = String.valueOf(row.getCell(j));
                    cell = getValue(row.getCell(j));
                    if (StringUtils.equals(cell, "null")){
                       cell = "";
                    }
                    stringBuffer.append(cell + ",");
                }
                String strArray = stringBuffer.toString();
                String[] strArrays = strArray.substring(0, strArray.length()- 1).split(",");
                list.add(strArrays);
            }
            if (CollectionUtils.isNotEmpty(list) && list.size() >= 2){
                list.remove(0);
                list.remove(0);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }
    /**
     * 读取excel表头
     * @param file
     */
    public static List<List<String>> readExcelHeader(MultipartFile file){
        List<List<String>> list = new ArrayList<>();
        try {
            File fo = new File(file.getOriginalFilename());
            FileUtils.copyInputStreamToFile(file.getInputStream(),fo);
            // 构造 Workbook 对象，execelFile 是传入文件路径(获得Excel工作区)
            Workbook book = null;
            try {
                // Excel 2007获取方法
                book = new XSSFWorkbook(FileUtils.openInputStream(fo));
            } catch (Exception ex) {
                // Excel 2003获取方法
                book = new HSSFWorkbook(FileUtils.openInputStream(fo));
            }

            // 读取表格的第一个sheet页
            Sheet sheet = book.getSheetAt(0);
            // 定义 row、cell
            Row row;
            String cell;
            // 总共有多少行,从0开始
            int totalRows = sheet.getLastRowNum() ;
            // 循环输出表格中的内容,首先循环取出行,再根据行循环取出列
            for (int i = 0; i <= 0; i++) {
                row = sheet.getRow(i);
                // 处理空行
                if(row == null){
                    continue ;
                }
                // 总共有多少列,从0开始
                int totalCells = row.getLastCellNum() ;
                StringBuffer stringBuffer = new StringBuffer();
                for (int j = row.getFirstCellNum(); j < totalCells; j++) {
                    // 处理空列
                    /*if(row.getCell(j) == null){
                        continue ;
                    }*/
                    // 通过 row.getCell(j).toString() 获取单元格内容
                    //cell = String.valueOf(row.getCell(j));
                    cell = getValue(row.getCell(j));
                    if (StringUtils.equals(cell, "null")){
                        cell = "";
                    }
                    stringBuffer.append(cell + ",");
                }
                String strArray = stringBuffer.toString();
                String[] strArrays = strArray.substring(0, strArray.length()- 1).split(",");
                if (strArrays != null && strArrays.length != 0){
                    List<String> rowList = Arrays.asList(strArrays);
                    list.add(rowList);
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 导入Excel
     * @param file
     */
    public static List<Map<String,String>> readExcelAllData(MultipartFile file, List<String> listTableHead){
        List<Map<String,String>> list = new ArrayList<>();
        try {
            File fo = new File(file.getOriginalFilename());
            FileUtils.copyInputStreamToFile(file.getInputStream(),fo);
            // 构造 Workbook 对象，execelFile 是传入文件路径(获得Excel工作区)
            Workbook book = null;
            try {
                // Excel 2007获取方法
                book = new XSSFWorkbook(FileUtils.openInputStream(fo));
            } catch (Exception ex) {
                // Excel 2003获取方法
                book = new HSSFWorkbook(FileUtils.openInputStream(fo));
            }

            // 读取表格的第一个sheet页
            Sheet sheet = book.getSheetAt(0);
            // 定义 row、cell
            Row row;
            String cell;
            // 总共有多少行,从0开始
            int totalRows = sheet.getLastRowNum() ;
            // 循环输出表格中的内容,首先循环取出行,再根据行循环取出列
            for (int i = 0; i <= totalRows; i++) {
                row = sheet.getRow(i);
                // 处理空行
                if(row == null){
                    continue ;
                }
                // 总共有多少列,从0开始
                int totalCells = row.getLastCellNum() ;
                //将单元格数据封装成map
                Map<String, String> map = new LinkedHashMap<>();
                for (int j = row.getFirstCellNum(); j < totalCells; j++) {
                    // 处理空列
                    /*if(row.getCell(j) == null){
                        continue ;
                    }*/
                    // 通过 row.getCell(j).toString() 获取单元格内容
                    //cell = String.valueOf(row.getCell(j));
                    cell = getValue(row.getCell(j));
                    if (StringUtils.equals(cell, "null")){
                        cell = "";
                    }
                    for (int k = 0; k < listTableHead.size(); k++) {
                        if (j == k){
                            map.put(listTableHead.get(k), cell);
                        }
                    }
                }
                list.add(map);
            }
            if (CollectionUtils.isNotEmpty(list) && list.size() >= 2){
                list.remove(0);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }
    public static String getValue(Cell cell){

        String value = "";
        if(null==cell){
            return value;
        }
        switch (cell.getCellType()) {
            //数值型
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //如果是date类型则 ，获取该cell的date值
                    Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                    value = format.format(date);
                }else {// 纯数字
                    BigDecimal big=new BigDecimal(cell.getNumericCellValue());
                    value = big.toString();
                }
                break;
            //字符串类型
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue().toString();
                break;
            // 公式类型
            case Cell.CELL_TYPE_FORMULA:
                //读公式计算值
                value = String.valueOf(cell.getNumericCellValue());
                if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
                    value = cell.getStringCellValue().toString();
                }
                break;
            // 布尔类型
            case Cell.CELL_TYPE_BOOLEAN:
                value = " "+ cell.getBooleanCellValue();
                break;
            // 空值
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            // 故障
            case Cell.CELL_TYPE_ERROR:
                value = "";
                break;
            default:
                value = cell.getStringCellValue().toString();
        }
        if("null".endsWith(value.trim())){
            value="";
        }
        return value;

    }
}
